
             
/**
 * 
 *
 * Copyright (c) 2012-2013 JLLT Corp. 2012-2013,All Rights Reserved.
 * This software is published under the 金麟龙腾商贸有限公司.
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file. 
 *
 * @File name:  TCategoryDao.java 
 * @Create on:  2012-06-28 21:05:412
 * @Author      :  高宇舟
 *
 * @ChangeList
 * ---------------------------------------------------
 * Date         Editor              ChangeReasons
 *
 */
           
        package com.boho.ecp.base.dao;

            
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import cn.boho.framework.po.CQExp;
import cn.boho.framework.po.CQuery;
import cn.boho.framework.po.CQueryFactoryTool;
import cn.boho.framework.po.DAO;

import com.boho.ecp.po.TCategoryPO;
            
        /**
 * 【类目信息:T_CATEGORY】的Dao操作类
 * 
 */
public class TCategoryDao extends DAO {
	
	/**
	* 根据条件查询【类目信息:T_CATEGORY|】信息
	* @param con
	* @param fCategoryId 父类目标识
	* @param isValid 是否有效
	* @return
	* @throws Exception
	* @date2012-07-10
	* @author 高宇舟
	* 
	*/
	public static List queryTCategoryByFCategoryID(Connection con,java.lang.Integer fCategoryId,java.lang.Integer isValid)throws Exception{
		CQExp cqExp = CQExp.instance().select("select T_CATEGORY.CATEGORY_NAME,T_CATEGORY.CATEGORY_ID,T_CATEGORY.F_CATEGORY_ID from T_CATEGORY ")
			.where()
			.filed("T_CATEGORY.F_CATEGORY_ID",CQExp.EQ,fCategoryId)
			.filed("T_CATEGORY.IS_VALID",CQExp.EQ,isValid)
			;
		System.out.println(cqExp.getSql());
		return cqExp.getDynaBeanMapList("T_CATEGORY",con);
	}

	
	/**
	* 查询所有【类目信息:T_CATEGORY】信息（树模式）
	* @param con
	* @param categoryId
	* @param categoryLevel
	* @return
	* @throws Exception
	* @date 2012-06-25
	* @author 高宇舟
	*
	*/
	public static List<TCategoryPO> queryTCategoryForTreeMode(Connection con,int categoryId,int categoryLevel) throws Exception {
		List<TCategoryPO> list = new LinkedList<TCategoryPO>();
		StringBuffer sql = new StringBuffer();
		sql.append("{call findLChild(")
			.append(categoryId)
			.append(",")
			.append(categoryLevel)
			.append(")}");
		CallableStatement cs = con.prepareCall(sql.toString());
		ResultSet rs = cs.executeQuery();
		while(rs.next()) {
			if (rs.getInt("CATEGORY_ID") != categoryId) {
				TCategoryPO po = new TCategoryPO();
				po.setCategoryId(rs.getInt("CATEGORY_ID"));
				po.setCategoryName(rs.getString("CATEGORY_NAME"));
				po.setFCategoryId(rs.getInt("F_CATEGORY_ID"));
				list.add(po);
			}
		}
		return list;
	}
	
	/**
	* 根据类目标识查询【类目信息:T_CATEGORY|】所有父类信息
	* @param con
	* @param categoryId 父类目标识
	* @return
	* @throws Exception
	* @date2012-08-15
	* @author 高宇舟
	* 
	*/
	public static List queryTCategoryByCategoryID(Connection con,java.lang.Integer categoryId)throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T2.CATEGORY_ID, T2.CATEGORY_NAME,T2.F_CATEGORY_ID ");
		sql.append("FROM (");  
		sql.append("    SELECT");    
		sql.append("        @r AS _id,");    
		sql.append("        (SELECT @r := T_CATEGORY.F_CATEGORY_ID FROM T_CATEGORY WHERE T_CATEGORY.CATEGORY_ID = _id) AS PID,");    
		sql.append("        @l := @l + 1 AS LVL");    
		sql.append("    FROM");    
		sql.append("        (SELECT @r := ?, @l := 0) VARS,");    
		sql.append("        T_CATEGORY T");
		sql.append("    WHERE @r <> 0) T1");    
		sql.append("  JOIN T_CATEGORY T2");   
		sql.append("  ON T1._id = T2.CATEGORY_ID");
		sql.append(" ORDER BY T1.LVL DESC"); 
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setCommand(sql.toString());
		query.setInt(1, categoryId);
		return query.getDynaBeanMapList("T_CATEGORY", con);
	}
}
